package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import entity.Course;
import entity.Student;
import utils.DBUtils;

public class CrsDao {
	
	//查询操作
	/**
	 * 查看所有用户
	 * @return 一个用户列表
	 * @throws ClassNotFoundException 
	 */
	
	//1.Query All
	public List<Course> findAll() throws ClassNotFoundException {	
		String sql = "select * from course;";
		
		Connection conn;
		try {
			conn = DBUtils.getCon();
			PreparedStatement ps = null;
			ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			
			List<Course> crsList = new ArrayList<Course>();
			
			while(rs.next()) {
				Course crs = new Course();
				crs.setSno(rs.getInt("Sno"));
				crs.setCname(rs.getString("Cname"));
				crs.setCscore(rs.getInt("Cscore"));
				crsList.add(crs);
			}
			
			DBUtils.release(conn, ps, rs);
			return crsList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	//2.Query by Full Sname
	public List<Student> findByFullName(String name) throws ClassNotFoundException {	
		String sql = "select * from student where Sname = ? order by Sno asc";
		
		Connection conn;
		try {
			conn = DBUtils.getCon();
			PreparedStatement ps = null;
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ResultSet rs = ps.executeQuery();
			
			List<Student> stuList = new ArrayList<Student>();
			
			while(rs.next()) {
				Student stu = new Student();
				stu.setSno(rs.getInt("Sno"));
				stu.setSname(rs.getString("Sname"));
				stu.setSsex(rs.getString("Ssex"));
				stu.setSage(rs.getInt("Sage"));
				stu.setSdept(rs.getString("Sdept"));
				stu.setSclass(rs.getInt("Sclass"));
				stuList.add(stu);
			}
			
			DBUtils.release(conn, ps, rs);
			return stuList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	//3.Query by Abstract Sname
	public List<Student> findByAbstractName(String name) throws ClassNotFoundException {	
		String sql = "select * from student where Sname like ? order by Sno asc";
		
		Connection conn;
		try {
			conn = DBUtils.getCon();
			PreparedStatement ps = null;
			ps = conn.prepareStatement(sql);
			ps.setString(1, "%" + name + "%");
			ResultSet rs = ps.executeQuery();
			
			List<Student> stuList = new ArrayList<Student>();
			
			while(rs.next()) {
				Student stu = new Student();
				stu.setSno(rs.getInt("Sno"));
				stu.setSname(rs.getString("Sname"));
				stu.setSsex(rs.getString("Ssex"));
				stu.setSage(rs.getInt("Sage"));
				stu.setSdept(rs.getString("Sdept"));
				stu.setSclass(rs.getInt("Sclass"));
				stuList.add(stu);
			}
			
			DBUtils.release(conn, ps, rs);
			return stuList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
		
	//4.Query by Sno
	public List<Course> findByNo(Integer sno) throws ClassNotFoundException {	
		String sql = "select * from course where Sno = ?";
		
		Connection conn;
		try {
			conn = DBUtils.getCon();
			PreparedStatement ps = null;
			ps = conn.prepareStatement(sql);
			ps.setInt(1, sno);
			ResultSet rs = ps.executeQuery();
			
			List<Course> crsList = new ArrayList<Course>();
			
			while(rs.next()) {
				Course crs = new Course();
			
				crs.setSno(rs.getInt("Sno"));
				crs.setCname(rs.getString("Cname"));
				crs.setCscore(rs.getInt("Cscore"));
				
				crsList.add(crs);
			}
			
			DBUtils.release(conn, ps, rs);
			return crsList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	
	//5.Add Student
	public void addInfo(String sname, String ssex, Integer sage, String sdept, Integer sclass) throws ClassNotFoundException {	
		String sql = "insert into student("
				+ " Sname, Ssex, Sage, Sdept, Sclass"
				+ ") values ("
				+ "?, ?, ?, ?, ?"
				+ ");";
		
		Connection conn;
		try {
			conn = DBUtils.getCon();
			PreparedStatement ps = null;
			ps = conn.prepareStatement(sql);
			ps.setString(1, sname);
			ps.setString(2, ssex);
			ps.setInt(3, sage);
			ps.setString(4, sdept);
			ps.setInt(5, sclass);
			
			ps.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	//6.Update Student
		public void updateInfo(Student student) throws ClassNotFoundException {	
			String sql = "update student set"
					+ " Sname = ?"
					+ " ,Ssex = ?"
					+ " ,Sage = ?"
					+ " ,Sdept = ?"
					+ " ,Sclass = ?"
					+ " where Sno = ?;";
			
			Connection conn;
			try {
				conn = DBUtils.getCon();
				PreparedStatement ps = null;
				ps = conn.prepareStatement(sql);
				ps.setString(1, student.getSname());
				ps.setString(2, student.getSsex());
				ps.setInt(3, student.getSage());
				ps.setString(4, student.getSdept());
				ps.setInt(5, student.getSclass());
				ps.setInt(6, student.getSno());
				ps.executeUpdate();
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}

		//7.Delete Student
		public void deleteInfo(Integer sno) throws ClassNotFoundException {	
			String sql = "delete from student  where Sno = ?;";
			
			Connection conn;
			try {
				conn = DBUtils.getCon();
				PreparedStatement ps = null;
				ps = conn.prepareStatement(sql);
				ps.setInt(1, sno);
				ps.executeUpdate();
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	
	
}